package net.cmkj.mine.bean;

import com.jfinal.aop.Before;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.IAtom;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import net.cmkj.mine.bean.base.BaseColumns;
import net.cmkj.mine.common.model.InvokeResult;
import net.cmkj.mine.util.BeanUtils;
import net.cmkj.mine.util.CommonUtils;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Columns extends BaseColumns<Columns> {

	public static final Columns dao = new Columns().dao();

	private static Logger log = LogManager.getLogger(Columns.class);

	/**
	 * 获取字段列表
	 * @param pageNumber
	 * @param pageSize
	 * @param moduleid
	 * @param name
	 * @param status
	 * @param orderbyStr
	 * @return
	 */
	public Page<Columns> getList(int pageNumber, int pageSize, long moduleid, String name, Integer status, String orderbyStr) throws Exception {
		log.info("获取字段列表");
		List<Object> params = new ArrayList<Object>();
		StringBuffer sqlExceptSelect=new StringBuffer(" from columns t left join dict_data dd on t.column_type = dd.value and dd.type_value = 'columntype' where t.moduleid = ? ");
		params.add(moduleid);
		if(CommonUtils.isNotEmpty(name)){
			sqlExceptSelect.append(" and t.column_comment like ? ");
			params.add("%"+name+"%");
		}
		if(status != null){
			sqlExceptSelect.append(" and t.status = ? ");
			params.add(status);
		}
		sqlExceptSelect.append(" "+orderbyStr);
		return dao.paginate(pageNumber, pageSize, "SELECT t.*, dd.name as columntype ", sqlExceptSelect.toString(), CommonUtils.listToArr(params));
	}

	public List<Columns> getList(long moduleid, String name, Integer status, String orderbyStr) throws Exception {
		log.info("获取字段列表");
		List<Object> params = new ArrayList<Object>();
		StringBuffer sqlExceptSelect=new StringBuffer(" from columns t left join dict_data dd on t.column_type = dd.value and dd.type_value = 'columntype' where t.moduleid = ? ");
		params.add(moduleid);
		if(CommonUtils.isNotEmpty(name)){
			sqlExceptSelect.append(" and t.column_comment like ? ");
			params.add("%"+name+"%");
		}
		if(status != null){
			sqlExceptSelect.append(" and t.status = ? ");
			params.add(status);
		}
		sqlExceptSelect.append(" "+orderbyStr);
		return dao.find( "SELECT t.*, dd.name as columntype "+ sqlExceptSelect.toString(), CommonUtils.listToArr(params));
	}

	/**
	 * 获取字段列表
	 * @param moduleid
	 * @return
	 * @throws Exception
	 */
	public List<Columns> getList(long moduleid) throws Exception {
		log.info("获取字段列表");
		return dao.find("select t.id as value, t.column_comment as title ,column_type as type from columns t where t.moduleid = ? order by t.ordinal_position", moduleid);
	}

	/**
	 * 获取显示字段列表
	 * @param moduleid
	 * @return
	 * @throws Exception
	 */
	public List<Columns> getShowList(long moduleid) throws Exception {
		log.info("获取显示字段列表");
		return dao.find("select t.* from columns t where t.moduleid = ? and t.is_show = 1 order by t.ordinal_position", moduleid);
	}
	/**
	 * 获取导入字段列表
	 * @param moduleid
	 * @return
	 * @throws Exception
	 */
	public List<Columns> getInportList(long moduleid) throws Exception {
		log.info("获取导入字段列表");
		return dao.find("select t.* from columns t where t.moduleid = ? and t.is_show = 1 AND column_type != 'file' order by t.ordinal_position", moduleid);
	}

	public int getCount(long moduleid) throws Exception {
		return Db.queryInt("select count(t.id) from columns t where t.status = 1 and t.moduleid = ?", moduleid);
	}

	/**
	 * 获取字段信息
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public Columns findById(long id) throws Exception {
		log.info("获取对象");
		return dao.findFirst("select t.*, dd.name as columntype from columns t left join dict_data dd on t.column_type = dd.value and dd.type_value = 'columntype' where t.id = ?", id);
	}

	/**
	 * 根据位置获取字段信息
	 * @param moduleid
	 * @param position
	 * @return
	 * @throws Exception
	 */
	public Columns findByPosition(long moduleid, int position) throws Exception {
		log.info("获取对象");
		return dao.findFirst("select t.* from columns t where t.moduleid = ? and t.ordinal_position = ?", new Object[]{moduleid, position});
	}

	/**
	 * 获取最终位置字段
	 * @param moduleid
	 * @return
	 * @throws Exception
	 */
	public Columns getLastColumn(long moduleid) throws Exception {
		return dao.findFirst("select t.*, dd.name as columntype from columns t left join dict_data dd on t.column_type = dd.value and dd.type_value = 'columntype' where t.moduleid = ? order by t.ordinal_position desc limit 0, 1", moduleid);
	}

	/**
	 * 库表字段保存
	 * @param column
	 * @return
	 * @throws Exception
	 */
	public boolean save(Columns column) throws Exception {
		log.info("对象保存");
		boolean flag = false;
		if (null == column.getId()) {
			flag = column.save();
		} else {
			Columns src = column.findById(column.getId());
			log.info(BeanUtils.compare(src, column));
			flag = column.update();
		}
		return flag;
	}

	/**
	 * 更新大于position的所有字段的position
	 * @param moduleid
	 * @param position
	 * @return
	 * @throws Exception
	 */
	public boolean updatePosition(long moduleid, int position) throws Exception {
		Db.update("update columns set ordinal_position = ordinal_position + 1 where moduleid = ? and ordinal_position >= ?", new Object[]{moduleid, position});
		return true;
	}


	/**
	 * 修改列表展示状态
	 * @param ids
	 * @param isshow
	 * @return
	 */
	@Before(Tx.class)
	public InvokeResult setColumnShow(String ids, Integer isshow) throws Exception{
		log.info("修改列表展示状态");
		String idarr[] = ids.split(",");
		for(String id : idarr){
			if (!dao.findById(id).set("is_show", isshow).update()) {
				log.error("批量修改列表展示状态！");
				throw new RuntimeException(id+"批量修改列表展示状态！");
			}
		}
		return InvokeResult.success();
	}


	/**
	 * 获取表字段信息
	 * @param tablename
	 * @return
	 * @throws Exception
	 */
	public List<Record> getColumnsList(String tablename) throws Exception {
		return Db.find("select t.column_comment, t.column_name, t.data_type, dd.name as columntype, t.column_type, t.is_nullable, t.column_default, t.ordinal_position from information_schema.columns t left join dict_data dd on t.data_type = dd.value and dd.type_value = 'columntype' where t.table_name = ? and t.column_name != 'id' order by t.ordinal_position", tablename);
	}
	
	/**
	 * 获取表字段
	 * @param tablename
	 * @param column
	 * @param param
	 * @return
	 * @throws Exception
	 */
	public Record getColumn(String tablename, String column, Object param) throws Exception {
		return Db.findFirst("select t.column_comment, t.column_name, t.data_type, dd.name as columntype, t.column_type, t.is_nullable, t.column_default, t.ordinal_position from information_schema.columns t left join dict_data dd on t.data_type = dd.value and dd.type_value = 'columntype' where t.table_name = ? and t."+column+" = ?", new Object[] {tablename, param});
	}
	
	/**
	 * 获取最终位置字段
	 * @param tablename
	 * @return
	 * @throws Exception
	 */
	public Record getLastColumn(String tablename) throws Exception {
		return Db.findFirst("select t.column_comment, t.column_name, t.data_type, dd.name as columntype, t.column_type, t.is_nullable, t.column_default, t.ordinal_position from information_schema.columns t left join dict_data dd on t.data_type = dd.value and dd.type_value = 'columntype' where t.table_name = ? order by t.ordinal_position desc limit 0, 1", tablename);
	}

	/**
	 * 创建库表
	 * @param dropstr
	 * @param createstr
	 * @return
	 * @throws Exception
	 */
	public boolean createTable(String dropstr, String createstr) throws Exception {
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Db.update(dropstr);
				Db.update(createstr);
				return true;
			}
		});
		return flag;
	}
	/**
	 * 删除库表
	 * @param tablename
	 * @return
	 * @throws Exception
	 */
	public boolean deleteTable(String tablename) throws Exception {
        String dropstr = "DROP TABLE IF EXISTS "+tablename;
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Db.update(dropstr);
				return true;
			}
		});
		return flag;
	}

	public boolean addColumns(String tablename, String columnstr, long moduleid, String fieldnum) throws Exception {
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				try {
					String tablestr = "ALTER TABLE "+tablename+" ADD COLUMN "+columnstr;
					Db.update(tablestr);
					Modules module = Modules.dao.findById(moduleid);
					module.setLastCnum(fieldnum);
					module.update();
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}
				return true;
			}
		});
		return flag;
	}
	
	public boolean editColumns(String tablename, String columnstr) throws Exception {
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				String tablestr = "ALTER TABLE "+tablename+" MODIFY COLUMN "+columnstr;
				Db.update(tablestr);
				return true;
			}
		});
		return flag;
	}
	public boolean deleteColumns(String tablename, String columnstr) throws Exception {
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				String tablestr = "ALTER TABLE "+tablename+" DROP COLUMN "+columnstr;
				Db.update(tablestr);
				return true;
			}
		});
		return flag;
	}

	/**
	 * 创建附件表
	 * @param tablename
	 * @return
	 * @throws Exception
	 */
	public boolean createFileTable(String tablename) throws Exception {
		//String dropstr = "DROP TABLE IF EXISTS "+tablename+"_attach;";
		String createstr = "CREATE TABLE if not exists "+tablename+"_attach (" +
				"`id`  bigint NOT NULL AUTO_INCREMENT ," +
				"`name`  varchar(255) NULL COMMENT '附件名称' ," +
				"`realname`  varchar(255) NULL COMMENT '文件实际名称' ," +
				"`parentid`  bigint NULL DEFAULT 0 COMMENT '所属功能' ," +
				"PRIMARY KEY (`id`)" +
				");";
		return Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				//Db.update(dropstr);
				Db.update(createstr);
				return true;
			}
		});
	}
	/**
	 * 删除附件表
	 * @param tablename
	 * @return
	 * @throws Exception
	 */
	public boolean deleteFileTable(String tablename) throws Exception {
		String dropstr = "DROP TABLE IF EXISTS "+tablename+"_attach;";
		return Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Db.update(dropstr);
				return true;
			}
		});
	}
	/**
	 * 字段移动
	 * @param id
	 * @param position
	 * @return
	 * @throws Exception
	 */
	public boolean move(long id, int position, int colpos) throws Exception {
		return Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				try {
					Columns current = findById(id);
					Columns tarCol = findByPosition(current.getModuleid(), colpos);
					if (!current.setOrdinalPosition(colpos).update()) {
						return false;
					}
					if (!tarCol.setOrdinalPosition(position).update()) {
						return false;
					}
				} catch (Exception e) {
					e.printStackTrace();
					log.error("位置调整异常："+e.getMessage());
				}
				return true;
			}
		});
	}

	/**
	 * 表字段移动
	 * @param tablename
	 * @param columnstr
	 * @param target
	 * @return
	 * @throws Exception
	 */
	public boolean move(String tablename, String columnstr, String target) throws Exception {
		String sqlPara = "ALTER TABLE "+tablename+" MODIFY COLUMN "+columnstr+" after "+target;
		int i = Db.update(sqlPara);
		if (i == 0) {
			return true;
		}
		return false;
	}

    /**
     * 更新大于position的所有字段的position
     * @param moduleid
     * @return
     * @throws Exception
     */
    public boolean deleteByModuleid(String moduleid) throws Exception {
        Db.update("delete from columns where moduleid=?",moduleid);
        return true;
    }
	
}
